package com.cskaoyan.mall.dao;

import com.cskaoyan.mall.model.Goods;
import com.cskaoyan.mall.model.Type;
import com.cskaoyan.mall.model.bo.*;
import com.cskaoyan.mall.model.vo.*;
import com.cskaoyan.mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class GoodsDaoImpl implements GoodsDao{
    @Override
    public List<Type> getType() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Type> typeList = null;
        try {
            typeList = runner.query("select * from type",new BeanListHandler<>(Type.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return typeList;
    }

    @Override
    public List<Goods> getGoodsByType(String typeId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Goods> goodsList = null;
        try {
            goodsList = runner.query("select * from goods where typeId = ? ",new BeanListHandler<>(Goods.class),typeId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return goodsList;
    }

    @Override
    public void addGoods(Goods goods) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into goods values (null,?,?,?,?,?,?)",
                    goods.getName(),
                    goods.getImg(),
                    goods.getTypeId(),
                    goods.getPrice(),
                    goods.getStockNum(),
                    goods.getDesc());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public int getLastId() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //注意这里的数据类型的对应
        BigInteger query = null;
        try {
            query = (BigInteger) runner.query("select last_insert_id()",new ScalarHandler());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query.intValue();
    }

    @Override
    public void addSpecs(List<AddSpecBO> specList, int goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        for (AddSpecBO addSpecBO : specList) {
            try {
                runner.update("insert into spec values(null,?,?,?,?)",
                        addSpecBO.getSpecName(),
                        addSpecBO.getStockNum(),
                        addSpecBO.getUnitPrice(),
                        goodsId);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public int addType(AddTypeBO addTypeBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into type values (null,?)",
                    addTypeBO.getName());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }

    /**
     * 这里我们只在type表中删除，而不是将goods表中的数据一起删除
     * @param id
     * @return
     */
    @Override
    public int deleteType(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from type where id = ? ",id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }
    /*
    {"code":0,"data":{"specs:":[{"id":17,"specName":"64G","stockNum":100,"unitPrice":5499.0},{"id":18,"specName":"128G","stockNum":200,"unitPrice":5899.0},{"id":19,"specName":"256G","stockNum":300,"unitPrice":6699.0}],"goods":{"id":10,"img":"http://localhost:8084/upload/f/1/9/7/1/2/7/3/7f2182b3-d13b-4eed-8c1a-486e167c1e19-iPhone11.jpg","name":"iPhone11","desc":"黑色 移动联通电信4G手机 双卡双待","typeId":4,"unitPrice":0.0}}}
    {"code":0,"data":{"specs":[{"id":1326,"specName":"坨","stockNum":0,"unitPrice":1.0},{"id":1509,"specName":"123","stockNum":123,"unitPrice":123.0},{"id":1510,"specName":"1234","stockNum":1234,"unitPrice":1234.0}],"goods":{"id":516,"img":"http://115.29.141.32:8084/static/image/15965544244494dc3315317aa0825.jpg","name":"老八特供2","desc":"略1111","typeId":1,"unitPrice":0.0}}}
     */
    @Override
    public Map<String, Object> getGoodsInfo(String id) {
        GetGoodsVO getGoodsVO = getGoodsVO(id);
        List<GetSpecVO> specVOList = getSpecVO(id);
        Map<String,Object> map = new HashMap<>();
        map.put("specs",specVOList);
        map.put("goods",getGoodsVO);
        return map;
    }
    /*
    private Integer id;
    private String specName;
    private Integer stockNum;
    private Double unitPrice;

    `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `stocknum` int(11) NULL DEFAULT NULL,
  `unitprice` double(10, 2) NULL DEFAULT NULL,
  `goodsId` int(11) NULL DEFAULT NULL,
     */
    private List<GetSpecVO> getSpecVO(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<GetSpecVO> specVOList = null;
        try {
            specVOList = runner.query("select id , specName , stockNum , unitPrice from spec where goodsid = ? ",new BeanListHandler<>(GetSpecVO.class),id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return specVOList;
    }

    /*
    private Integer id;
    private String img;
    private String name;
    private String desc;
    private Integer typeId;

    id,name,img,typeId,price,stockNum,desc
     */
    private GetGoodsVO getGoodsVO(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        GetGoodsVO getGoodsVO = null;
        try {
            getGoodsVO = runner.query("select id , img , `name` , `desc` , typeId from goods where id = ? ",new BeanHandler<>(GetGoodsVO.class),id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return getGoodsVO;
    }

    /**
     * 修改商品详情时使用的修改规格
     * id,name,stockNum,unitprice,goodsid
     * @return
     */
    @Override
    public int addSpec(AddSpecsBO addSpecsBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into spec values (null,?,?,?,?)",
                    addSpecsBO.getSpecName(),
                    addSpecsBO.getStockNum(),
                    addSpecsBO.getUnitPrice(),
                    Integer.parseInt(addSpecsBO.getGoodsId()));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }

    /**
     *
     * Goods goods = new Goods(Integer.parseInt(updateGoodsBO.getId()),updateGoodsBO.getName(),null,updateGoodsBO.getTypeId(), price, stockNum,updateGoodsBO.getDesc());
     * @param goods
     * @return
     */
    /*
        desc: "可爱，小萌妹必备"
        id: "3"
        name: "2020可爱背包"
        specList: [{id: 5, specName: "小号", stockNum: 200, unitPrice: 50},…]
        0: {id: 5, specName: "小号", stockNum: 200, unitPrice: 50}
        1: {id: 6, specName: "大号", stockNum: 150, unitPrice: 55}
        2: {goodsId: "3", specName: "XXXL码", stockNum: "1", unitPrice: "1"}
        typeId: 2
         */
    @Override
    public int updateGoods(Goods goods) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update goods set price = ? , `name` = ? , `desc` = ? , typeId = ? , stockNum = ? , img = ? where id = ? ",
                    goods.getPrice(),
                    goods.getName(),
                    goods.getDesc(),
                    goods.getTypeId(),
                    goods.getStockNum(),
                    goods.getImg(),
                    goods.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }

    @Override
    public int updateSpecs(List<UpdateSpecBO> specList, Integer id) {
        //{id: 5, specName: "小号", stockNum: 200, unitPrice: 50
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        for (UpdateSpecBO updateSpecBO : specList) {
            try {
                runner.update("update spec set specName = ? , stockNum = ? , unitPrice = ? where goodsId = ? and id = ? ",
                        updateSpecBO.getSpecName(),
                        updateSpecBO.getStockNum(),
                        updateSpecBO.getUnitPrice(),
                        id,
                        updateSpecBO.getId());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                return 404;
            }
        }
        return 200;
    }

    @Override
    public int deleteSpec(DeleteSpecBO deleteSpecBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from spec where goodsId = ? and specName = ? ",
                    deleteSpecBO.getGoodsId(),
                    deleteSpecBO.getSpecName());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }

    @Override
    public int deleteGoods(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from goods where id = ? ", id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }
    /*
     * {"code":0,
     * "data":
     * [{"id":357,
     * "userId":503,
     * "goodsId":610,
     * "content":"kanyixia",
     * "state":1,
     * "createtime":"2020-08-10 22:58:39",
     * "goods":{"name":"xiaomi"},
     * "user":{"name":"debug"}},
     * id orderid content state createtime
     */
    @Override
    public List<MsgVO> noReplyMsg() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<MsgVO> msgVOS = null;
        MsgGoodsVO msgGoodsVO = null;
        MsgUserVO msgUserVO = null;
        try {
            msgVOS = runner.query("select * from msg where state = 1",new BeanListHandler<>(MsgVO.class));
            for (MsgVO msgVO : msgVOS) {
                msgGoodsVO = runner.query("select name from goods where id = ? ",new BeanHandler<>(MsgGoodsVO.class),msgVO.getGoodsId());
                msgUserVO = runner.query("select nickname as name from user where id = ? ",new BeanHandler<>(MsgUserVO.class),msgVO.getUserId());
                msgVO.setGoods(msgGoodsVO);
                msgVO.setUser(msgUserVO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return msgVOS;
    }

    @Override
    public List<MsgReplyVO> repliedMsg() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<MsgReplyVO> msgReplyVOS = null;
        MsgGoodsVO msgGoodsVO = null;
        MsgUserVO msgUserVO = null;
        try {
            msgReplyVOS = runner.query("select * from msg where state = 0",new BeanListHandler<>(MsgReplyVO.class));
            for (MsgReplyVO msgVO : msgReplyVOS) {
                msgGoodsVO = runner.query("select name from goods where id = ? ",new BeanHandler<>(MsgGoodsVO.class),msgVO.getGoodsId());
                msgUserVO = runner.query("select nickname as name from user where id = ? ",new BeanHandler<>(MsgUserVO.class),msgVO.getUserId());
                msgVO.setGoods(msgGoodsVO);
                msgVO.setUser(msgUserVO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return msgReplyVOS;
    }

    @Override
    public int reply(ReplyBO replyBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update msg set state = 0 and replyContent = ? where id = ? ",replyBO.getContent(),replyBO.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }
}
